package com.coolwen.controller;

import com.coolwen.entity.Student;
import com.coolwen.service.student.StudentService;
import jxl.Workbook;
import jxl.biff.DisplayFormat;
import jxl.format.*;
import jxl.format.Alignment;
import jxl.format.VerticalAlignment;
import jxl.write.*;
import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.RequestMethod;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

/**
 * Created by nieyu on 2016/8/4.
 */
@Controller
@RequestMapping("/student")
public class StudentController {

    private Logger logger = Logger.getLogger(getClass());

    @Autowired
    StudentService studentService;

    @RequestMapping(value = "/student_info", method = RequestMethod.GET)
    public String getStudentInfo(Model model, String grade, String department, String major,HttpServletRequest request) throws UnsupportedEncodingException {

        model.addAttribute("g", grade);
        model.addAttribute("d", department);
        model.addAttribute("m", major);

        if (department != null) {
            String subDepartment = request.getParameter("department");
            department = subDepartment.substring(0, 2);
        }

        model.addAttribute("pagers", studentService.find(grade, department, major));

        return "page/index";
    }

    /*
    *学生评教信息导出
     */
    @RequestMapping(value = "/out_info", method = RequestMethod.GET)
    public String putStudentInfo(String grade, String department, String major, HttpServletResponse response) throws IOException, WriteException {

        OutputStream os = response.getOutputStream();// 取得输出流
        response.reset();// 清空输出流

        SimpleDateFormat dt = new SimpleDateFormat("yyyyMMddHHmmss");
        String date = dt.format(new Date());//获取当前时间

        response.setHeader("Content-disposition", "attachment; filename=" + new String(date.getBytes("GB2312"), "8859_1") + ".xls");// 设定输出文件头
        response.setContentType("application/msexcel");// 定义输出类型
        WritableWorkbook wwb;

        System.out.println("**"+grade+"++"+department+"--"+major);
        List<Student> students = studentService.findAll(grade, department, major);


        try {
            //设置单元格文本自动换行
            WritableFont fontTitle = new WritableFont(WritableFont.createFont("宋体"), 10, WritableFont.NO_BOLD);
            WritableCellFormat formatTitle = new WritableCellFormat(fontTitle);
            formatTitle.setWrap(true);

            WritableCellFormat headerFormat = new WritableCellFormat(
                    fontTitle);

            //设置单元格文本居中显示

            //水平居中
            headerFormat.setAlignment(Alignment.CENTRE);
            //垂直居中
            headerFormat.setVerticalAlignment(VerticalAlignment.CENTRE);

        /*
         * WritableFont.createFont("宋体")：设置字体为宋体
         * 10：设置字体大小
         * WritableFont.BOLD:设置字体加粗（BOLD：加粗     NO_BOLD：不加粗）
         * false：设置非斜体
         * UnderlineStyle.NO_UNDERLINE：没有下划线
         */
            WritableFont font = new WritableFont(WritableFont.createFont("宋体"),
                    10,
                    WritableFont.BOLD,
                    false,
                    UnderlineStyle.NO_UNDERLINE);

            WritableCellFormat formatTitles = new WritableCellFormat(font);
            formatTitles.setWrap(true);

            WritableCellFormat headerFormats = new WritableCellFormat(
                    font);

            //设置单元格文本居中显示

            //水平居中
            headerFormats.setAlignment(jxl.format.Alignment.CENTRE);
            //垂直居中
            headerFormats.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

            wwb = Workbook.createWorkbook(os);
            WritableSheet ws = wwb.createSheet("学生评教", 0);

            ws.mergeCells(1, 0, 18, 1);//合并单元格，第一个参数：要合并的单元格最左上角的列号，
            // 第二个参数：要合并的单元格最左上角的行号，
            // 第三个参数：要合并的单元格最右角的列号，
            // 第四个参数：要合并的单元格最右下角的行号，
            ws.mergeCells(0, 0, 0, 1);

            ws.setColumnView(1, 15);

            for (int i = 0; i < 1; i++) {
                Student student = students.get(i);
                ws.addCell(new Label(1, 0, "四川旅游学院本科(" + student.getGrade() + "级" + student.getMajor() + "专业）专业课程质量（学生）评价统计表", headerFormats));
            }
            ws.mergeCells(0, 2, 0, 3);
            ws.addCell(new Label(0, 2, "序号", headerFormat));
            ws.mergeCells(1, 2, 1, 3);
            ws.addCell(new Label(1, 2, "课程名称", headerFormat));
            ws.mergeCells(2, 2, 5, 2);
            ws.addCell(new Label(2, 2, "第一学年（上/下）", headerFormat));
            ws.addCell(new Label(2, 3, "参评人数"));
            ws.addCell(new Label(3, 3, "平均分数"));
            ws.addCell(new Label(4, 3, "参评人数"));
            ws.addCell(new Label(5, 3, "平均分数"));
            ws.mergeCells(6, 2, 9, 2);
            ws.addCell(new Label(6, 2, "第二学年（上/下）", headerFormat));
            ws.addCell(new Label(6, 3, "参评人数"));
            ws.addCell(new Label(7, 3, "平均分数"));
            ws.addCell(new Label(8, 3, "参评人数"));
            ws.addCell(new Label(9, 3, "平均分数"));
            ws.mergeCells(10, 2, 13, 2);
            ws.addCell(new Label(10, 2, "第三学年（上/下）", headerFormat));
            ws.addCell(new Label(10, 3, "参评人数"));
            ws.addCell(new Label(11, 3, "平均分数"));
            ws.addCell(new Label(12, 3, "参评人数"));
            ws.addCell(new Label(13, 3, "平均分数"));
            ws.mergeCells(14, 2, 17, 2);
            ws.addCell(new Label(14, 2, "第四学年（上/下）", headerFormat));
            ws.addCell(new Label(14, 3, "参评人数"));
            ws.addCell(new Label(15, 3, "平均分数"));
            ws.addCell(new Label(16, 3, "参评人数"));
            ws.addCell(new Label(17, 3, "平均分数"));
            ws.mergeCells(18, 2, 18, 3);
            ws.addCell(new Label(18, 2, "总评得分", headerFormat));

            for (int i = 0; i < students.size(); i++) {

                int b = i + 1;
                Student student = students.get(i);
                int grades = Integer.valueOf(student.getGrade()).intValue();

                String ses = student.getSemster().substring(0, 4);
                String semster = student.getSemster().substring(11, 15);
                int c = Integer.valueOf(ses).intValue();

                int reduce = c - grades;

                ws.addCell(new Label(0, i + 4, "" + b, headerFormat));
                ws.addCell(new Label(1, i + 4, "" + student.getCourse(), formatTitle));

                if (semster.equals("第一学期") && reduce == 0) {
                    ws.addCell(new Label(2, i + 4, String.valueOf(student.getNumbers()), headerFormat));
                    ws.addCell(new Label(3, i + 4, "" + String.valueOf(student.getScore()), headerFormat));
                } else if (semster.equals("第二学期") && reduce == 0) {
                    ws.addCell(new Label(4, i + 4, String.valueOf(student.getNumbers()), headerFormat));
                    ws.addCell(new Label(5, i + 4, "" + String.valueOf(student.getScore()), headerFormat));
                } else if (semster.equals("第一学期") && reduce == 1) {
                    ws.addCell(new Label(6, i + 4, String.valueOf(student.getNumbers()), headerFormat));
                    ws.addCell(new Label(7, i + 4, "" + String.valueOf(student.getScore()), headerFormat));
                } else if (semster.equals("第二学期") && reduce == 1) {
                    ws.addCell(new Label(8, i + 4, String.valueOf(student.getNumbers()), headerFormat));
                    ws.addCell(new Label(9, i + 4, "" + String.valueOf(student.getScore()), headerFormat));
                } else if (semster.equals("第一学期") && reduce == 2) {
                    ws.addCell(new Label(10, i + 4, String.valueOf(student.getNumbers()), headerFormat));
                    ws.addCell(new Label(11, i + 4, "" + String.valueOf(student.getScore()), headerFormat));
                } else if (semster.equals("第二学期") && reduce == 2) {
                    ws.addCell(new Label(12, i + 4, String.valueOf(student.getNumbers()), headerFormat));
                    ws.addCell(new Label(13, i + 4, "" + String.valueOf(student.getScore()), headerFormat));
                } else if (semster.equals("第一学期") && reduce == 3) {
                    ws.addCell(new Label(14, i + 4, String.valueOf(student.getNumbers()), headerFormat));
                    ws.addCell(new Label(15, i + 4, "" + String.valueOf(student.getScore()), headerFormat));
                } else if (semster.equals("第二学期") && reduce == 3) {
                    ws.addCell(new Label(16, i + 4, String.valueOf(student.getNumbers()), headerFormat));
                    ws.addCell(new Label(17, i + 4, "" + String.valueOf(student.getScore()), headerFormat));
                }
                ws.addCell(new Label(18, i + 4, "" + String.valueOf(student.getScore()), headerFormat));
            }

            wwb.write();
            //关闭Excel工作薄对象
            wwb.close();

        } catch (IOException e) {
            e.printStackTrace();
        }

        return "page/out";

    }

    /*
    *学生评教模板下载
     */
    @RequestMapping(value = "/download", method = RequestMethod.GET)
    public void downloadStudent(HttpServletResponse response) throws IOException, WriteException {

        SimpleDateFormat dt = new SimpleDateFormat("yyyyMMddHHmmss");
        String date = dt.format(new Date());//获取当前时间

        OutputStream os = response.getOutputStream();// 取得输出流
        response.reset();// 清空输出流

        response.setHeader("Content-disposition", "attachment; filename=" + new String("学生评教模板".getBytes("GB2312"), "8859_1") + ".xls");// 设定输出文件头
        response.setContentType("application/msexcel");// 定义输出类型
        WritableWorkbook wwb;

        try {
            //设置单元格文本自动换行
            WritableFont fontTitle = new WritableFont(WritableFont.TIMES, 9, WritableFont.NO_BOLD);
            WritableCellFormat formatTitle = new WritableCellFormat(fontTitle);
            formatTitle.setWrap(true);

            WritableCellFormat headerFormat = new WritableCellFormat(
                    fontTitle);

            //设置单元格文本居中显示

            //水平居中
            headerFormat.setAlignment(jxl.format.Alignment.CENTRE);
            //垂直居中
            headerFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

             /*
         * WritableFont.createFont("宋体")：设置字体为宋体
         * 10：设置字体大小
         * WritableFont.BOLD:设置字体加粗（BOLD：加粗     NO_BOLD：不加粗）
         * false：设置非斜体
         * UnderlineStyle.NO_UNDERLINE：没有下划线
         */
            WritableFont font = new WritableFont(WritableFont.createFont("宋体"),
                    16,
                    WritableFont.BOLD,
                    false,
                    UnderlineStyle.NO_UNDERLINE);

            WritableCellFormat formatTitles = new WritableCellFormat(font);
            formatTitles.setWrap(true);

            WritableCellFormat headerFormats = new WritableCellFormat(
                    font);

            //设置单元格文本居中显示

            //水平居中
            headerFormats.setAlignment(jxl.format.Alignment.CENTRE);
            //垂直居中
            headerFormats.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

            WritableFont font2 = new WritableFont(WritableFont.createFont("宋体"),
                    10,
                    WritableFont.BOLD,
                    false,
                    UnderlineStyle.NO_UNDERLINE);

            WritableCellFormat formatTitlet = new WritableCellFormat(font2);
            formatTitlet.setWrap(true);

            WritableCellFormat headerFormats2 = new WritableCellFormat(
                    font2);

            //设置单元格文本居中显示

            //水平居中
            headerFormats2.setAlignment(jxl.format.Alignment.CENTRE);
            //垂直居中
            headerFormats2.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);

            wwb = Workbook.createWorkbook(os);
            WritableSheet ws = wwb.createSheet("学生评教模板", 0);

            ws.mergeCells(1, 0, 8, 0);

            ws.mergeCells(1, 1, 8, 1);

            ws.setColumnView(1, 15);
            ws.setColumnView(2, 15);
            ws.setColumnView(3, 15);
            ws.setColumnView(4, 15);
            ws.setColumnView(5, 15);
            ws.setColumnView(6, 15);
            ws.setColumnView(7, 15);
            ws.setColumnView(8, 15);

            ws.setRowView(0, 1170, false);

            ws.addCell(new Label(1, 0, "四川旅游学院教学考评结果(学生评)", headerFormats));

            ws.addCell(new Label(1, 1, "2014-2015学年第二学期", headerFormats2));

            ws.addCell(new Label(1, 2, "年级", headerFormat));

            ws.addCell(new Label(2, 2, "专业", headerFormat));

            ws.addCell(new Label(3, 2, "课程名称", headerFormat));

            ws.addCell(new Label(4, 2, "参评人数", headerFormat));

            ws.addCell(new Label(5, 2, "应评人数", headerFormat));

            ws.addCell(new Label(6, 2, "平均分", headerFormat));

            ws.addCell(new Label(7, 2, "参评率", headerFormat));

            ws.addCell(new Label(8, 2, "院系", headerFormat));


            wwb.write();
            //关闭Excel工作薄对象
            wwb.close();

        } catch (IOException e) {
            e.printStackTrace();
        }

    }
}
